GetData method

4.1 GetData method

The generic read method for the web service is called GetData. This method accepts a string XML representation of a SQL statement and runs it against a specified view.

Note: The name of the view passed in is automatically prefixed with WS_. See section 2.1.2, Data source restrictions.

4.1.1 Query XML format

You must pass in well-formed XML. The Table node is mandatory; all other nodes are optional.

<Parameters>
    <Table> Table name </Table>
    <Distinct />
    <Field><FieldName> Name of a field in the table or view </FieldName></Field>
    ...
    <Where>
        <FieldName> Field name </FieldName>
        <FieldValue> Field value </FieldValue>
        <FieldType> Field type </FieldType>
        <Operation> Operation to use </Operation>
        <Conjunction> Whether the nodes are joined with AND or OR. </Conjunction>
    </Where>
    ...
    <OrderBy>
        <OrderFieldName> Field name </OrderFieldName>
        <Desc> Yes|No </Desc>
    </OrderBy>
    ...
    <MaxRecords> Maximum number of records </MaxRecords>
</Parameters>

4.1.1.1 Table

The name of the table or view in the MyID database. Do not specify the WS_ prefix; it is added automatically. For example, if you want to view the data from the WS_CARDPROFILES view, use the following:

<Table>CARDPROFILES</Table>

4.1.1.2 Distinct

If <Distinct /> is specified, the DISTINCT keyword is added to the SQL query.

4.1.1.3 Field

You can specify multiple Field nodes. If no Field nodes are specified, all fields within the specified view are returned. Otherwise only fields specified are returned.

For example the following specifies SELECT DeviceSerialNumber, DeviceTypeName FROM ...

<Field><FieldName>DeviceSerialNumber</FieldName></Field>

<Field><FieldName>DeviceTypeName</FieldName></Field>

4.1.1.4 Where

For each <Where> node, a filter criteria is added, filtering the results that are returned.

If no <Where> nodes are specified, no WHERE clause are added to the SQL query.

Each <Where> node needs the following subnodes:

<FieldName> specifies the name of the field that to be filtered.
<FieldValue> specifies the value to be filtered against. If the Operation node is equals (=) or not equals (!=), wildcards are allowed, which perform a LIKE or NOT LIKE query. You can have multiple FieldValue nodes for IN operations.

The following wildcards are supported:
- * – represents any sequence of characters (equivalent to SQL %).
- ? – represents a single character (equivalent to SQL _).
<FieldType> must be one of the following values:
- string – The field being filtered is a string value.
- long – The field being filtered is a numeric value.
- datestring – The field being filtered is a datetime.
If no FieldType is specified, it defaults to string.
<Operation> must be one of the following values:
- = – performs an equals or like query.
- != – performs a not equals or not like query.
- lt – less than.
- gt – greater than.
- le – less than or equals.
- ge – greater than or equals.
- isnull – field is null.
- notnull – field is not null.
- IN – perform an IN query for each specified <FieldValue> node. This is the only operation that uses multiple <FieldValue> nodes.
If no operation is specified it defaults to equals (=).
<Conjunction> – if you omit this node, all <Where> nodes are joined with AND conjunctions. To specify an OR query, set the <Conjunction> node to or.

4.1.1.5 OrderBy

Allows ordering of the returned results set. You can omit this node, or include multiple <OrderBy> nodes. Each <OrderBy> has the following subnodes:

<OrderFieldName> – the field name on which to sort.
<Desc> – This optional node allows sorting in descending order. If Desc = Yes, the results are returned in descending order. If the node is not present, results are sorted in ascending order.

4.1.1.6 MaxRecords

<MaxRecords> – Specifies the maximum number of records to return.

If you do not specify a value, MaxRecords defaults to 1000.

4.1.2 Example queries

This section contains example queries and returned results.

4.1.2.1 Example one

<Parameters>
<Table>CARDPROFILES</Table>
</Parameters>

The above query returns all records and all fields in the WS_CARDPROFILES view.

An example returned XML file is:

<?xml version="1.0" encoding="utf-8"?>
<recordset>
<record>
    <Name>Startup Profile</Name>
    <Description>A basic profile to get the system going.</Description>
    <Version>1</Version>
    <ID>2</ID>
</record>
<record>
    <Name>PIV Card</Name>
    <Description>A basic PIV card</Description>
    <Version>1</Version>
    <ID>3</ID>
</record>
<record>
    <Name>Manager</Name>
    <Description>One year system access</Description>
    <Version>7</Version>
    <ID>10</ID>
</record>
<record>
    <Name>Cardholder</Name>
    <Description>
    </Description>
    <Version>3</Version>
    <ID>12</ID>
</record>
</recordset>

4.1.2.2 Example two

<Parameters>
    <Table>CARDPROFILES</Table>
    <Field><FieldName>Name</FieldName></Field>
    <Field><FieldName>Description</FieldName></Field>
    <Where>
        <FieldName>Version</FieldName>
        <FieldValue>1</FieldValue>
        <FieldType>long</FieldType>
        <Operation>=</Operation>
    </Where>
    <OrderBy>
        <OrderFieldName>Name</OrderFieldName>
        <Desc>Yes</Desc>
    </OrderBy>
    <MaxRecords>10</MaxRecords>
</Parameters>

The above query returns the Name and Description fields from the WS_CARDPROFILES view, filtering on a long field called Version to return only those rows where Version = 1. A maximum of 10 records is returned, ordered by the Name in descending order.

An example returned XML file is:

<?xml version="1.0" encoding="utf-8"?>
<recordset>
<record>
    <Name>Startup Profile</Name>
    <Description>A basic profile to get the system going.</Description>
    <ID>2</ID>
</record>
<record>
    <Name>PIV Card</Name>
    <Description>A basic PIV card</Description>
    <ID>3</ID>
</record>
</recordset>